package com.wanxi.dao.Impl;

import com.wanxi.dao.CustomerDao;
import com.wanxi.model.CustomerModel;
import com.wanxi.model.UserModel;
import com.wanxi.util.JDBC;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author Li
 * @Date 2023/5/6 21:07
 */
public class CustomerDaoImpl implements CustomerDao {
    public int add(CustomerModel customerModel) {
        String sql = "INSERT INTO `customers` (`name`, `age`, `phone`, `sex`," +
                " `address`, `email`, `create_time`, `update_time`, " +
                "`enable`, `remark`, `lawyer_id`) " +
                "VALUES ('" + customerModel.getName() + "', '" + customerModel.getAge() + "', '" + customerModel.getPhone() + "', " +
                "'" + customerModel.getSex() + "', '" + customerModel.getAddress() + "', '" + customerModel.getEmail() + "', now(), " +
                "sysdate(), '" + customerModel.getEnable() + "', '" + customerModel.getRemark() + "', '" + customerModel.getLawyerId() + "');";
        return JDBC.update(sql);
    }

    public int update(CustomerModel customerModel) {
        String sql = "UPDATE `customers` SET `name` = '" + customerModel.getName() + "', `age` = '" + customerModel.getAge() + "', `phone` = '" + customerModel.getPhone() + "'," +
                " `sex` = '" + customerModel.getSex() + "', `address` = '" + customerModel.getAddress() + "', `email` = '" + customerModel.getEmail() + "', " +
                " `update_time` = now(), `lawyer_id` = '" + customerModel.getLawyerId() + "' WHERE (`id` = '" + customerModel.getId() + "');";
        return JDBC.update(sql);
    }

    public int delete(CustomerModel customerModel) {
        String sql = "delete from `customers` where id='" + customerModel.getId() + "' ";
        return JDBC.update(sql);
    }

    public List<CustomerModel> findAll(CustomerModel customerModel) {
        int start = (customerModel.getPage()-1)*customerModel.getLimit();
        String sql = "select * from `customers` where true";
        sql += getWhereSql(customerModel);
        sql += " order by id desc";
        sql += " limit "+start+","+customerModel.getLimit()+" ";
        ResultSet query = JDBC.query(sql);
        List<CustomerModel> customerModelList = new ArrayList<CustomerModel>();
        try {
            while (query.next()) {
                customerModelList.add(CustomerModel.builder()
                        .id(query.getInt("id"))
                        .name(query.getString("name"))
                        .age((query.getInt("age")))
                        .sex(query.getString("sex"))
                        .email(query.getString("email"))
                        .address(query.getString("address"))
                        .phone(query.getString("phone"))
                        .remark(query.getString("remark"))
                        .createTime(query.getString("create_time"))
                        .enable(query.getInt("enable"))
                        .lawyerId(query.getInt("lawyer_id"))
                        .build());
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return customerModelList;
    }
    private String getWhereSql(CustomerModel customerModel){
        String sql = "";
        if (!"".equals(customerModel.getName())) {
            sql += " and name like '%" + customerModel.getName() + "%' ";
        }
        if (!"".equals(customerModel.getSex())) {
            sql += " and sex='" + customerModel.getSex() + "' ";
        }
        if (!"".equals(customerModel.getPhone())) {
            sql += " and phone like '%" + customerModel.getPhone() + "%' ";
        }
        if (!"".equals(customerModel.getAddress())) {
            sql += " and address like '%" + customerModel.getAddress() + "%' ";
        }
        if (customerModel.getEnable()!=-1) {
            sql += " and enable = '" + customerModel.getEnable() + "' ";
        }
        if (customerModel.getStartAge()!=-1){
            sql += " and age >= '"+customerModel.getStartAge()+"' ";
        }
        if (customerModel.getEndAge()!=-1){
            sql += " and age <= '"+customerModel.getEndAge()+"' ";
        }
        return sql;
    }

    /**
     * 根据id查询
     *
     * @param customerModel
     * @return
     */
    public CustomerModel findById(CustomerModel customerModel) {
        String sql = " select * from `customers` where id = '" + customerModel.getId() + "' ";
        ResultSet query = JDBC.query(sql);
        try {
            while (query.next()){
              return CustomerModel.builder()
                      .id(query.getInt("id"))
                      .name(query.getString("name"))
                      .age(query.getInt("age"))
                      .sex(query.getString("sex"))
                      .phone(query.getString("phone"))
                      .address(query.getString("address"))
                      .email(query.getString("email"))
                      .enable(query.getInt("enable"))
                      .remark(query.getString("remark"))
                      .lawyerId(query.getInt("lawyer_id"))
                      .build();
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }

    public Integer count(CustomerModel customerModel) {
        String sql = "select count(*) as count from `customers` where true";
        sql += getWhereSql(customerModel);

        return JDBC.count(sql);
    }

    @Override
    public Integer totalCount() {
        String sql = "select count(*) as count from `customers`";
        return JDBC.count(sql);
    }

    public boolean findSameName(CustomerModel model) {
        String sql = "select * from customers where name='" + model.getName() + "' ";
        ResultSet query = JDBC.query(sql);
        try {
            while (query.next()) {
                return true;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return false;
    }

    @Override
    public boolean findSamePhone(CustomerModel model) {
        String sql = "select * from customers where phone='" + model.getPhone() + "' ";
        ResultSet query = JDBC.query(sql);
        try {
            while (query.next()) {
                return true;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return false;
    }

    @Override
    public boolean findSameEmail(CustomerModel model) {
        String sql = "select * from customers where email='" + model.getEmail() + "' ";
        ResultSet query = JDBC.query(sql);
        try {
            while (query.next()) {
                return true;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return false;
    }
}
